package chapter10.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UsersDao {
    private Connection conn;

    public UsersDao(Connection conn) {
        this.conn = conn;
    }

    public User getById(int id) {
        ResultSet rs = null;
        PreparedStatement stmt = null;
        try {
            // 3.通过Connection对象获取 PrepareStatement 对象
            stmt = conn.prepareStatement("select * from users where id = ?");
            stmt.setInt(1, id);
            // 4.使用Statement执行SQL语句。
            rs = stmt.executeQuery();
            // 5. 操作ResultSet结果集
            System.out.println("id	|	name	|	password|	email		|	birthday");
            if (rs.next()) {
                id = rs.getInt("id"); // 通过列名获取指定字段的值
                String name = rs.getString("name");
                String psw = rs.getString("password");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                System.out.println(id + "	|	" + name + "	|	" + psw +
                        "	|	" + email + "	|	" + birthday);

                User student = new User(id, name, psw, email, birthday);
                return student;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(stmt);
            IOUtils.close(rs);
        }
        return null;
    }

    public List<User> getAll() {
        List<User> ret = new ArrayList<>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 3.通过Connection对象获取Statement对象
            stmt = conn.createStatement();
            // 4.使用Statement执行SQL语句。
            String sql = "select * from users";
            rs = stmt.executeQuery(sql);
            // 5. 操作ResultSet结果集
            System.out.println("id	|	name	|	password|	email		|	birthday");
            while (rs.next()) {
                int id = rs.getInt("id"); // 通过列名获取指定字段的值
                String name = rs.getString("name");
                String psw = rs.getString("password");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                System.out.println(id + "	|	" + name + "	|	" + psw +
                        "	|	" + email + "	|	" + birthday);

                User student = new User(id, name, psw, email, birthday);
                ret.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(stmt);
            IOUtils.close(rs);
        }
        return ret;
    }

    public int insert(User user) {
        int newRecordId = -1;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            // 3.通过Connection对象获取 PrepareStatement 对象
            stmt = conn.prepareStatement(
                    "insert into users(name,password,email,birthday) " +
                            "values (?,?,?,?)"
                    , Statement.RETURN_GENERATED_KEYS
            );
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getEmail());
            stmt.setDate(4, user.getBirthday());
            // 4.使用Statement执行SQL语句。
            int count = stmt.executeUpdate();
            // 5.打印结果
            System.out.println("添加了 " + count + " 条");

            // 6.新记录的自增id
            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                newRecordId = rs.getInt(1);
                return newRecordId;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(stmt);
        }
        return newRecordId;
    }

    public int update(User user) {
        int count = 0;
        PreparedStatement stmt = null;
        try {
            // 3.通过Connection对象获取 PrepareStatement 对象
            stmt = conn.prepareStatement(
                    "update users set name=?,password=?,email=?,birthday=? " +
                            "where id=?"
            );
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getEmail());
            stmt.setDate(4, user.getBirthday());
            stmt.setInt(5, user.getId());

            // 4.使用Statement执行SQL语句。
            count = stmt.executeUpdate();
            // 5.打印结果
            System.out.println("更新了 " + count + " 条");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(stmt);
        }
        return count;
    }

    public int delete(int id) {
        int count = 0;
        PreparedStatement stmt = null;
        try {
            // 3.通过Connection对象获取 PrepareStatement 对象
            stmt = conn.prepareStatement("delete from users where id = ?");
            stmt.setInt(1, id);
            // 4.使用Statement执行SQL语句。
            count = stmt.executeUpdate();
            // 5.打印结果
            System.out.println("删除了 " + count + " 条");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(stmt);
        }
        return count;
    }

}
